import pandas, duckdb
from config import ini_op
from openpyxl import  load_workbook
import os
from utils.path_utils import get_project_root
from database.duckdb.duckdb_base import get_connection


def run(filepath, timenow):
    # workbook = load_workbook(filepath, data_only=True)
    # sheet = workbook.active
    df = pandas.read_excel(filepath, dtype=str)
    conn = get_connection()
    conn.register('temp_wldw', df)
    conn.execute("""
        delete from t_api_wldw where code in (select 编码 from temp_wldw);
        insert into t_api_wldw (
            id,
            code,
            name_chs,
            organizationcode,
            type,
            area,
            countryorregion,
            seclevel,
            seclevelid,
            state_asyncdeletestatus,
            state_isenabled,
            state_disabletime,
            timestamp_createdon,
            timestamp_lastchangedon,
            esun_api_update)
        select 'esun-' || substr(cast(uuid() as varchar), 6),编码,名称,社会信用代码,类别,地区,国家或地区,0,'BFPUBLIC','0',
            case 
                 when 是否可用 = '是' then 1
                 when 是否可用 = '否' then 0
            end,
            停用时间,创建时间,最后修改时间,'"""+timenow+"""'
            from temp_wldw; 
        update t_api_wldw set type = '空' where type is null;
        """)
    conn.close()


if __name__ == '__main__':
    run(r'C:\Users\lzq32\Downloads\往来单位查询.xlsx', '20250515113745')